package ca.camosun.inventory.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import ca.camosun.inventory.Item;

public class BoxAccessor {

	public class DatabaseHelper extends SQLiteOpenHelper {

		public static final int DATABASE_VERSION = 2;
		public static final String DATABASE_NAME = "boxes.db";
		public static final String BOXES_TABLE_NAME = "BOXES";
		public static final String BOX_TABLE_NAME = "BOX";
		public static final String BOX_NAME = "BOX";
		public static final String BOX_IDENTIFIER = "BOX_CODE";
		public static final String ITEM_NAME = "ITEM";
		public static final String ITEM_DESC = "DESCRIPTION";
		public static final String ITEM_QUANTITY = "QUANTITY";

		private static final String BOXES_TABLE_CREATE = "CREATE TABLE "
				+ BOXES_TABLE_NAME + " (" 
				+ BOX_NAME + " TEXT UNIQUE,"
				+ BOX_IDENTIFIER + " TEXT UNIQUE, " +
				"PRIMARY KEY(" + BOX_NAME + ", " + BOX_IDENTIFIER + ")" +
				 ");";

		private static final String BOX_TABLE_CREATE = "CREATE TABLE "
				+ BOX_TABLE_NAME + " (" 
				+ BOX_NAME + " TEXT,"
				+ ITEM_NAME + " TEXT ," 
				+ ITEM_DESC + " TEXT,"
				+ ITEM_QUANTITY + " INTEGER," +
				  "PRIMARY KEY(" + BOX_NAME + ", " + ITEM_NAME + ")" +
				  ");";

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(BOXES_TABLE_CREATE);
			db.execSQL(BOX_TABLE_CREATE);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(BoxAccessor.class.getName(),
					"Upgrading database from version " + oldVersion + " to "
							+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + BOX_TABLE_CREATE);
			db.execSQL("DROP TABLE IF EXISTS " + BOXES_TABLE_CREATE);
			onCreate(db);

		}
	}

	// Database fields
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	public enum BoxList {
		BOX_ADDED("Box has been added"),
		ADD_FAILED("Failed to add box: Unknown"),
		ADD_FAILED_NO_BOX_NAME("Failed to add box: No Box Name entered"),
		ADD_FAILED_NO_CODE("Failed to add box: No Code entered"),
		ADD_FAILED_DUPLICATE_BOX("Failed to add box: Duplicate Box Name"),
		ADD_FAILED_DUPLICATE_CODE("Failed to add box: Duplicate Box Code");

		
		private final String visible;
		
		private BoxList(String humanVisible) {
			visible = humanVisible;
		}
		
		@Override
		public String toString() {
			return visible;
		}
	}

	public BoxAccessor(Context context) {
		dbHelper = new DatabaseHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public void deleteBox(String aBox) {
		String[] whereArgs = { aBox };

		database.delete(DatabaseHelper.BOXES_TABLE_NAME,
				DatabaseHelper.BOX_NAME + "=?", whereArgs);

	}

	public BoxList addBox(String aBox, String qrLocationCode) {
		if(aBox.equals("") == true) {
			return BoxList.ADD_FAILED_NO_BOX_NAME;
		}
		if(qrLocationCode.equals("") == true) {
			return BoxList.ADD_FAILED_NO_CODE;
		}
		
		ContentValues insertValues = new ContentValues();
        if (retrieveBoxByName(aBox).equals("") == false) {
            return BoxList.ADD_FAILED_DUPLICATE_BOX;
        }
		insertValues.put(DatabaseHelper.BOX_NAME, aBox);
        if (retrieveBoxByCode(qrLocationCode).equals("") == false) {
            return BoxList.ADD_FAILED_DUPLICATE_CODE;
        }
		insertValues.put(DatabaseHelper.BOX_IDENTIFIER, qrLocationCode);
		if( database.insert(DatabaseHelper.BOXES_TABLE_NAME, null, insertValues) == -1) {
			return BoxList.ADD_FAILED;
		}
		return BoxList.BOX_ADDED;
	}

	public void deleteItem(String aBox, String aItem) {

		String[] deleteColumn = new String[] { aBox, aItem };
		database.delete(DatabaseHelper.BOX_TABLE_NAME, DatabaseHelper.BOX_NAME
				+ "=? AND " + DatabaseHelper.ITEM_NAME + "=?", deleteColumn);

	}

	public void addItem(String aBox, String aItem, String description,
			int quantity) {

		ContentValues insertValues = new ContentValues();
		insertValues.put(DatabaseHelper.BOX_NAME, aBox);
		insertValues.put(DatabaseHelper.ITEM_NAME, aItem);
		insertValues.put(DatabaseHelper.ITEM_DESC, description);
		insertValues.put(DatabaseHelper.ITEM_QUANTITY, quantity);
		database.replace(DatabaseHelper.BOX_TABLE_NAME, null, insertValues);
	}

	public List<String> retrieveBoxes() {
		Cursor cursor = database.query(DatabaseHelper.BOXES_TABLE_NAME, null,
				null, null, null, null, null);
		cursor.moveToFirst();
		List<String> allBoxes = new ArrayList<String>();

		while (!cursor.isAfterLast()) {
			String boxName = cursor.getString(0);

			allBoxes.add(boxName);
			cursor.moveToNext();
		}
		cursor.close();

		return allBoxes;

	}
	
	public String retrieveBoxByName(String Name) {
		String[] whereArgs = { Name };
		Cursor cursor = database.query(DatabaseHelper.BOXES_TABLE_NAME, null,
				DatabaseHelper.BOX_NAME + "=?", whereArgs, null, null, null);
		cursor.moveToFirst();

		String boxName = "";
		
		while (!cursor.isAfterLast()) {
			boxName = cursor.getString(0);
			cursor.moveToNext();
		}
		cursor.close();

		return boxName;

	}
	
	public String retrieveBoxByCode(String qrLocationCode) {
		String[] whereArgs = { qrLocationCode };
		Cursor cursor = database.query(DatabaseHelper.BOXES_TABLE_NAME, null,
				DatabaseHelper.BOX_IDENTIFIER + "=?", whereArgs, null, null, null);
		cursor.moveToFirst();

		String boxName = "";
		
		while (!cursor.isAfterLast()) {
			boxName = cursor.getString(0);
			cursor.moveToNext();
		}
		cursor.close();

		return boxName;

	}

	public void removeBox(String boxName) {
		
		String[] deleteColumn = new String[] { boxName };
		database.delete(DatabaseHelper.BOXES_TABLE_NAME, DatabaseHelper.BOX_NAME
				+ "=?", deleteColumn);
		
		database.delete(DatabaseHelper.BOX_TABLE_NAME, DatabaseHelper.BOX_NAME
				+ "=?", deleteColumn);
	}

	public List<Item> retrieveBoxContents(String boxName) {
		String[] whereArgs = { boxName };
		Cursor cursor = database.query(DatabaseHelper.BOX_TABLE_NAME, null,
				DatabaseHelper.BOX_NAME + "=?", whereArgs, null, null, null);
		cursor.moveToFirst();
		List<Item> boxItems = new ArrayList<Item>();

		while (!cursor.isAfterLast()) {
			String itemName = cursor.getString(1);
			String itemDesc = cursor.getString(2);
			int itemQuantity = cursor.getInt(3);
			boxItems.add(new Item(itemName, itemDesc, itemQuantity));

			cursor.moveToNext();
		}
		cursor.close();

		return boxItems;

	}

	public Item retrieveItem(String fakeBox, String fakeItem) {
		String[] whereArgs = { fakeBox, fakeItem };
		Cursor cursor = database.query(DatabaseHelper.BOX_TABLE_NAME, null,
				DatabaseHelper.BOX_NAME + "=? AND " + DatabaseHelper.ITEM_NAME
						+ "=?", whereArgs, null, null, null);
		cursor.moveToFirst();
		Item aItem = new Item("Error", "Error", 1);
		String itemName = cursor.getString(1);
		String itemDesc = cursor.getString(2);
		int itemQuantity = cursor.getInt(3);
		aItem = new Item(itemName, itemDesc, itemQuantity);

		cursor.moveToNext();
		cursor.close();

		return aItem;

	}

	public Cursor searchItem(String inText) throws SQLException {
		
		return null;
	}


}
